import pandas as pd
import matplotlib.pyplot as plt
from datetime import datetime
import os

# 设置中文字体
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False

def analyze_vehicle_freight():
    try:
        # 检查文件是否存在
        if not os.path.exists('FhjlViewDD.xlsx'):
            print("错误：未找到FhjlViewDD.xlsx文件")
            return
        
        # 读取Excel文件
        df = pd.read_excel('FhjlViewDD.xlsx')
        
        # 检查必要列是否存在
        required_columns = ['创建时间', '车辆', '净重']
        missing_cols = [col for col in required_columns if col not in df.columns]
        if missing_cols:
            print(f"错误：缺少必要的列: {missing_cols}")
            print(f"可用列: {list(df.columns)}")
            return
        
        # 转换日期格式并筛选6月数据
        df['创建时间'] = pd.to_datetime(df['创建时间'], errors='coerce')
        if df['创建时间'].isnull().any():
            print("警告：部分日期格式不正确，已自动处理")
            
        june_data = df[(df['创建时间'].dt.month == 6) & (df['创建时间'].dt.year == datetime.now().year)]
        if len(june_data) == 0:
            print("错误：未找到当前年份6月份数据")
            return
        
        # 按车辆汇总货运量
        vehicle_stats = june_data.groupby('车辆')['净重'].sum().sort_values(ascending=False)
        
        # 打印结果
        print("6月各车辆货运量排名:")
        print(vehicle_stats.to_string())
        
        # 保存为Excel
        output_file = 'june_vehicle_freight_ranking_v4.xlsx'
        vehicle_stats.to_excel(output_file)
        print(f"结果已保存为: {output_file}")
        
    except Exception as e:
        print(f"发生错误: {str(e)}")

if __name__ == "__main__":
    analyze_vehicle_freight()